package vendas.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vendas.dto.User;
import vendas.dto.UserStatus;
import vendas.dto.UserType;


public class UserDAO extends DataAccessObject {

    public static User findByPK(int userId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_USER         CD_USER       ");
        sbSql.append("     , A.TX_NAME         TX_NAME       ");
        sbSql.append("     , A.TX_EMAIL        TX_EMAIL      ");
        sbSql.append("     , A.ID_STATUS       ID_STATUS     ");
        sbSql.append("     , A.ID_TYPE         ID_TYPE       ");
        sbSql.append("     , A.TX_PASSWORD     TX_PASSWORD   ");
        sbSql.append("     , A.TS_CREATION     TS_CREATION   ");
        sbSql.append("     , A.TS_LAST_ACCESS  TS_LAST_ACCESS");
        sbSql.append("  FROM TB_USER        A ");
        sbSql.append(" WHERE A.CD_USER = ? ");

        User user = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                user = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return user;
    }

    public static User findByEmail(String email, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_USER         CD_USER       ");
        sbSql.append("     , A.TX_NAME         TX_NAME       ");
        sbSql.append("     , A.TX_EMAIL        TX_EMAIL      ");
        sbSql.append("     , A.ID_STATUS       ID_STATUS     ");
        sbSql.append("     , A.ID_TYPE         ID_TYPE       ");
        sbSql.append("     , A.TX_PASSWORD     TX_PASSWORD   ");
        sbSql.append("     , A.TS_CREATION     TS_CREATION   ");
        sbSql.append("     , A.TS_LAST_ACCESS  TS_LAST_ACCESS");
        sbSql.append("  FROM TB_USER         A ");
        sbSql.append(" WHERE A.TX_EMAIL = ? ");

        User user = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setString(ps, 1, email);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                user = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.findByEmail (" + sqle.getMessage() + ")");
        }
        return user;
    }

    public static boolean existEmail(String email, Connection conn) throws TransactionException {

        return existEmail(email, 0, conn);
    }

    public static boolean existEmail(String email, int codigo, Connection conn) throws TransactionException {

        boolean found = false;
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT CD_USER   ");
        sbSql.append("  FROM TB_USER      ");
        sbSql.append(" WHERE TX_EMAIL = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setString(ps, 1, email);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int cdUser = getInt(rs, "CD_USER");
                found = codigo == 0 || cdUser != codigo;
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.existEmail (" + sqle.getMessage() + ")");
        }
        return found;
    }

    public static List<User> findAll(Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_USER         CD_USER       ");
        sbSql.append("     , A.TX_NAME         TX_NAME       ");
        sbSql.append("     , A.TX_EMAIL        TX_EMAIL      ");
        sbSql.append("     , A.ID_STATUS       ID_STATUS     ");
        sbSql.append("     , A.ID_TYPE         ID_TYPE       ");
        sbSql.append("     , A.TX_PASSWORD     TX_PASSWORD   ");
        sbSql.append("     , A.TS_CREATION     TS_CREATION   ");
        sbSql.append("     , A.TS_LAST_ACCESS  TS_LAST_ACCESS");
        sbSql.append("  FROM TB_USER       A ");
        sbSql.append(" ORDER BY A.TX_NAME ");
        List<User> usersList = new ArrayList<User>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User user = fromResultSet(rs);
                usersList.add(user);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.findAll (" + sqle.getMessage() + ")");
        }
        return usersList;
    }

    public static List<User> findByFilter(UserFilter filter, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_USER         CD_USER       ");
        sbSql.append("     , A.TX_NAME         TX_NAME       ");
        sbSql.append("     , A.TX_EMAIL        TX_EMAIL      ");
        sbSql.append("     , A.ID_STATUS       ID_STATUS     ");
        sbSql.append("     , A.ID_TYPE         ID_TYPE       ");
        sbSql.append("     , A.TX_PASSWORD     TX_PASSWORD   ");
        sbSql.append("     , A.TS_CREATION     TS_CREATION   ");
        sbSql.append("     , A.TS_LAST_ACCESS  TS_LAST_ACCESS");
        sbSql.append("  FROM TB_USER        A ");
        sbSql.append(" WHERE 1 = 1 ");
        if (filter.hasUserId()) {
            sbSql.append(" AND A.CD_USER = ? ");
        }
        if (filter.hasFirstDigit()) {
            String strLetra = filter.getFirstDigit().toLowerCase();
            if (strLetra.equalsIgnoreCase("0")) {
                sbSql.append(" AND LEFT(A.TX_NAME, 1) < 'A'");
            } else {
                sbSql.append(" AND LOWER(A.TX_NAME) like '" + strLetra + "%' ");
            }
        }
        if (filter.hasUserName()) {
            String strName = filter.getUserName().toLowerCase();
            sbSql.append(" AND LOWER(A.TX_NAME) like '%" + strName + "%' ");
        }
        if (filter.hasUserEmail()) {
            String strEmail = filter.getUserEmail().toLowerCase();
            sbSql.append(" AND LOWER(A.TX_EMAIL) like '%" + strEmail + "%' ");
        }
        if (filter.hasIdStatus()) {
            sbSql.append(" AND A.ID_STATUS = ? ");
        }
        if (filter.hasIdPerfil()) {
            sbSql.append(" AND A.ID_TYPE = ? ");
        }
        sbSql.append(" ORDER BY A.TX_NAME ");
        List<User> usersList = new ArrayList<User>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            if (filter.hasUserId()) {
                setInt(ps, pos++, filter.getUserId());
            }
            if (filter.hasIdStatus()) {
                setInt(ps, pos++, filter.getIdStatus());
            }
            if (filter.hasIdPerfil()) {
                setInt(ps, pos++, filter.getIdPerfil());
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User user = fromResultSet(rs);
                usersList.add(user);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.findByFilter (" + sqle.getMessage() + ")");
        }
        return usersList;
    }

    public static void insert(User user, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO TB_USER    ");
        sbSql.append("     ( CD_USER         ");
        sbSql.append("     , TX_NAME         ");
        sbSql.append("     , TX_EMAIL        ");
        sbSql.append("     , ID_STATUS       ");
        sbSql.append("     , ID_TYPE         ");
        sbSql.append("     , TX_PASSWORD     ");
        sbSql.append("     , TS_CREATION     ");
        sbSql.append(") VALUES (?,?,?,?,?,?,?)");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, user.getId());
            setString(ps, pos++, user.getName());
            setString(ps, pos++, user.getEmail());
            setInt(ps, pos++, user.getStatus().getId());
            setInt(ps, pos++, user.getType().getId());
            setString(ps, pos++, user.getPassword());
            setTimestamp(ps, pos++, user.getCreationDate());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(User user, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE TB_USER          ");
        sbSql.append("   SET TX_NAME      = ? ");
        sbSql.append("     , TX_EMAIL     = ? ");
        sbSql.append("     , ID_STATUS    = ? ");
        sbSql.append("     , ID_TYPE      = ? ");
        sbSql.append(" WHERE CD_USER      = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setString(ps, pos++, user.getName());
            setString(ps, pos++, user.getEmail());
            setInt(ps, pos++, user.getStatus().getId());
            setInt(ps, pos++, user.getType().getId());
            setInt(ps, pos++, user.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void updatePassword(User user, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE TB_USER         ");
        sbSql.append("   SET TX_PASSWORD = ? ");
        sbSql.append(" WHERE CD_USER     = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setString(ps, pos++, user.getPassword());
            setInt(ps, pos++, user.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.updatePassword (" + sqle.getMessage() + ")");
        }
    }

    public static void updateLastAccess(User user, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE TB_USER      ");
        sbSql.append("   SET TS_LAST_ACCESS = CURRENT_TIMESTAMP ");
        sbSql.append(" WHERE CD_USER  = ? ");

        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, user.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.updatePassword (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(User user, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM TB_USER  ");
        sbSql.append(" WHERE CD_USER = ?");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, user.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("UserDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static int getNextCodigo(Connection conn, int iRange, int eRange) throws TransactionException {

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT MAX(CD_USER) AS CD_MAX ");
            sbSql.append("  FROM TB_USER        ");
            sbSql.append(" WHERE CD_USER > ? ");
            sbSql.append("   AND CD_USER < ? ");

            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, iRange);
            setInt(ps, 2, eRange);
            ResultSet rs = ps.executeQuery();
            int max = 0;
            if (rs.next()) {
                max = rs.getInt("CD_MAX");
            }
            close(rs);
            return (max != 0 ? max : iRange) + 1;
        } catch (SQLException sqlEx) {
            throw new TransactionException(sqlEx);
        }
    }

    private static User fromResultSet(ResultSet rs) throws SQLException {

        // TB_USER
        User user = new User();
        user.setId(getInt(rs, "CD_USER"));
        user.setName(getString(rs, "TX_NAME"));
        user.setEmail(getString(rs, "TX_EMAIL"));
        user.setStatus(UserStatus.getUserStatusById(getByte(rs, "ID_STATUS")));
        user.setType(UserType.getUserTypeById(getByte(rs, "ID_TYPE")));
        user.setPassword(getString(rs, "TX_PASSWORD"));
        user.setLastAcess(getDateFromTimestamp(rs, "TS_LAST_ACCESS"));
        user.setCreationDate(getDateFromTimestamp(rs, "TS_CREATION"));
        //
        return user;
    }

}
